﻿/****************************** Module Header ******************************\
 * Module Name:  BusinessLogicBase.cs
 * Module Version:  1.0.0 (2011-12-16)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to the business logic layer. It supplies the common variables
 * and common entity methods.The business logic base class could extend this class for
 * standard the father class.
 * 
 * log:2012-12-05 by sutroon
 * 重构,由BusinessLogicBase改为BusinessHelper
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/

using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

namespace UCF.Core.Data.ORM
{
    /// <summary>
    /// 商业逻辑层基类
    /// </summary>
    public class BusinessHelper
    {
        /// <summary>
        /// 基类中的DBHelper实例
        /// </summary>
        protected DatabaseHelper dbHelper;

        /// <summary>
        /// 基类中的DALDBHelper实例
        /// </summary>
        public EntityDBHelper dalHelper;

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString { get; set; }

        /// <summary>
        /// 数据源提供程序名称
        /// </summary>
        public string ProviderName { get; set; }

        /// <summary>
        /// 基类中的DBParameters实例
        /// </summary>
        protected DbParams dbParameters;

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="primaryKey"></param>
        /// <param name="sortKey"></param>
        /// <param name="connectionName"></param>
        public BusinessHelper(string primaryKey, string sortKey, System.Configuration.ConnectionStringSettings connectionSetting)
        {
            ConnectionString = connectionSetting.ConnectionString;
            ProviderName = connectionSetting.ProviderName;
            this.dbHelper = new DatabaseHelper(ConnectionString,ProviderName);
            this.dalHelper = new EntityDBHelper(primaryKey, sortKey, ConnectionString, ProviderName);
            this.dbParameters = new DbParams(ProviderName);
        }

        /// <summary>
        /// 获取一个记录对象
        /// </summary>
        /// <param name="sqlStatement">不包含 where 开头的条件语句<para>如：UserName like '%so%'</para></param>
        /// <returns></returns>
        public virtual T GetEntity<T>(string sqlStatement) where T : class,new()
        {
            return this.dalHelper.GetEntity<T>(sqlStatement);
        }

        /// <summary>
        /// 获取一个记录对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public virtual T GetEntityById<T>(object id) where T : class,new()
        {
            return this.dalHelper.GetEntityById<T>(id);
        }

        /// <summary>
        /// 获取最后一条记录
        /// </summary>
        /// <returns></returns>
        public virtual T GetLastEntity<T>() where T : class,new()
        {
            return this.dalHelper.GetLastEntity<T>();
        }

        /// <summary>
        /// 获取一个记录列表
        /// </summary>
        /// <param name="sqlStatement">一条完整的SQL查询语句或者不包含 where 开头的条件语句</param>
        /// <returns></returns>
        public virtual IList<T> GetEntityList<T>(string sqlStatement, int count = 0) where T : new()
        {
            return this.dalHelper.GetEntityList<T>(sqlStatement, count);
        }

        /// <summary>
        /// 获取经过分页后的记录列表
        /// <para>2012-10-16 修改为数据库分页，效率提高了很多</para>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="orderBy"></param>
        /// <param name="pageid"></param>
        /// <param name="pagesize"></param>
        /// <param name="recordcount"></param>
        /// <param name="pagecount"></param>
        /// <returns></returns>
        public virtual IList<T> GetEntityPagingList<T>(string whereSql, string orderBySql, int pageid, int pagesize, out int recordcount, out int pagecount) where T : new()
        {
            recordcount = this.dalHelper.GetEntityCount<T>(whereSql);
            if (recordcount == 0)
            {
                pagecount = 0;
                return new List<T>();
            }
            pagecount = recordcount / pagesize;
            if (recordcount % pagesize > 0) pagecount += 1;
            return this.dalHelper.GetEntityPagingList<T>(whereSql, orderBySql, pageid, pagesize);
            //PaggerGeneric<T> dbp = new PaggerGeneric<T>(pageid, pagesize);
            //dbp.BindDataSource(this.GetEntityList<T>(sql));
            //recordcount = dbp.RecordCount;
            //pagecount = dbp.PageCount;
            //return dbp.ListSet;
        }
        public virtual DataTable GetEntityPagingTable<T>(string whereSql, string orderBySql, int pageid, int pagesize, out int recordcount, out int pagecount) where T : new()
        {
            recordcount = this.dalHelper.GetEntityCount<T>(whereSql);
            if (recordcount == 0)
            {
                pagecount = 0;
                return new DataTable();
            }
            pagecount = recordcount / pagesize;
            if (recordcount % pagesize > 0) pagecount += 1;
            return this.dalHelper.GetEntityPagingTable<T>(whereSql, orderBySql, pageid, pagesize);
        }


        /// <summary>
        /// 获取经过分页后的记录列表
        /// <para>2012-9-25</para>
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageid"></param>
        /// <param name="pagesize"></param>
        /// <param name="recordcount"></param>
        /// <param name="pagecount"></param>
        /// <returns></returns>
        public virtual DataTable GetPagingDataTable(string sql, int pageid, int pagesize, out int recordcount, out int pagecount)
        {
            PaggerDataTable dbp = new PaggerDataTable(pageid,pagesize);
            dbp.BindDataSource(this.GetDataTable(sql));
            recordcount = dbp.RecordCount;
            pagecount = dbp.PageCount;
            return dbp.ListSet;

        }
        /// <summary>
        /// 获取指定字段值的记录列表
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="v"></param>
        /// <param name="fieldname"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public virtual IList<T> GetEntityListByFieldValue<T, V>(object v, string fieldname, string condition = "") where T : class,new()
        {
            return this.dalHelper.GetEntityListByFieldValue<T, V>(v, fieldname, condition);
        }

        /// <summary>
        /// 通过上级分类编号获取列表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="domain"></param>
        /// <param name="pushshow"></param>
        /// <returns></returns>
        public virtual IList<T> GetEntityListByParentID<T, V>(object id, string domain, short pushshow = -1) where T : class,new()
        {
            return this.dalHelper.GetEntityListByFieldValue<T, V>(id, "ParentID", pushshow > -1 ? "DomainAddress='{1}' and PushShow={0} order by Ordinal".Fmt(pushshow, domain) : "order by Ordinal");
        }

        /// <summary>
        /// 获取指定扩展标签的列表
        /// </summary>
        /// <param name="tagName"></param>
        /// <param name="domain"></param>
        /// <returns></returns>
        public virtual IList<T> GetEntityListByPaireValue<T>(string tagName, string domain) where T : class,new()
        {
            return this.GetEntityList<T>("DomainAddress='{1}' and ExTags='{0}'".Fmt(tagName, domain));
        }

        /// <summary>
        /// 获取符合条件的所有主键的值的列表
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public virtual IList<V> GetEntityIDsList<T, V>(string condition, string field = "")
        {
            return this.dalHelper.GetIDsList<T, V>(condition, field);
        }

        /// <summary>
        /// 获取指定编号下所有子分类的数量
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="value"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public virtual int GetEntityCountByFieldValue<T, V>(V value, string fieldName = "ParentID")
        {
            return this.dalHelper.GetEntityCount<T>("{0}={1}".Fmt(fieldName, typeof(V) == typeof(int) ? "'{0}'".Fmt(value) : value.ToString()));
        }

        /// <summary>
        /// 获取表格
        /// </summary>
        /// <param name="sqlCondition"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public virtual DataTable GetEntityDataTable<T>(string sqlCondition = "", string fields = "*",int count=0)
        {
            if (sqlCondition.Length > 0 && sqlCondition.ToLower().Substring(0, 6) != "where ") sqlCondition = string.Format("where {0}", sqlCondition);
            string sql = string.Format("select {3}{0} from {1} {2} ", fields, typeof(T).Name, sqlCondition,count==0?"":" top {0} ".Fmt(count));
            return this.dbHelper.ExecuteDataTable(sql);
        }
        public virtual DataTable GetEntityDataTable_fh<T>(string sqlCondition = "", string fields = "*")
        {
            if (sqlCondition.Length > 0 && sqlCondition.ToLower().Substring(0, 6) != "where ") sqlCondition = string.Format("where {0}", sqlCondition);            
            string sql = string.Format("select {0} from {1} {2} order by ordinal asc ", fields, typeof(T).Name, sqlCondition);
            return this.dbHelper.ExecuteDataTable(sql);
        }

        public virtual DataTable GetEntityDataTable_User<T>(string sqlCondition = "", string fields = "*")
        {
            if (sqlCondition.Length > 0 && sqlCondition.ToLower().Substring(0, 6) != "where ") sqlCondition = string.Format("where {0}", sqlCondition);
            string sql = string.Format("select {0} from {1} {2} where len(DepartmentID)>0 ", fields, typeof(T).Name, sqlCondition);
            return this.dbHelper.ExecuteDataTable(sql);
        }

        /// <summary>
        /// 添加一条记录,并返回记录的主键值
        /// </summary>
        /// <param name="e"></param>
        /// <param name="lastRecordQueryCondition">获取最后一条语句的命令</param>
        /// <returns></returns>
        public virtual object InsertEntity<T>(T e, string lastRecordQueryCondition = "") where T : class,new()
        {
            return this.dalHelper.InsertEntity<T>(e, lastRecordQueryCondition);
        }

        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="e"></param>
        public virtual void AddEntity<T>(T e)
        {
            this.dalHelper.AddEntity(e);
        }

        /// <summary>
        /// 更新一条记录
        /// </summary>
        /// <param name="e"></param>
        public virtual void UpdateEntity<T>(T e,string condition="")
        {
            this.dalHelper.UpdateEntity(e,condition);
        }

        /// <summary>
        /// 更新记录字段
        /// </summary>
        /// <param name="id"></param>
        /// <param name="fieldsAndValues"></param>
        /// <param name="args"></param>
        public virtual void UpdateEntity<T>(object id, string fieldsAndValues, params object[] args)
        {
            this.dalHelper.UpdateEntity<T>(id, fieldsAndValues.Fmt(args));
        }

        /// <summary>
        /// 批量更新记录字段
        /// </summary>
        /// <param name="fieldsAndValues"></param>
        /// <param name="sqlCondition"></param>
        public virtual void UpdateEntities<T>(string fieldsAndValues, string sqlCondition)
        {
            this.dalHelper.UpdateEntity<T>(fieldsAndValues, sqlCondition);
        }

        /// <summary>
        /// 删除一条记录(逻辑删除)
        /// </summary>
        /// <param name="id"></param>
        public virtual void DeleteEntity<T>(object id)
        {
            this.dalHelper.UpdateEntity<T>(id, "IsDeleted=1");
        }

        /// <summary>
        /// 批量删除记录(逻辑删除)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        public virtual void DeleteEntities<T>(string condition)
        {
            this.dalHelper.UpdateEntity<T>("IsDeleted=1", condition);
        }

        /// <summary>
        /// 删除一条记录(物理删除)
        /// </summary>
        /// <param name="id"></param>
        public virtual void RemoveEntityById<T>(object id)
        {
            this.dalHelper.DeleteEntityById<T>(id);
        }

        /// <summary>
        /// 批量删除记录(物理删除)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        public void RemoveEntities<T>(string condition)
        {
            this.dalHelper.DeleteEntities<T>(condition);
        }

        /// <summary>
        /// 获取记录数量
        /// </summary>
        /// <param name="sqlCondition">不带where关键词的查询条件</param>
        /// <returns></returns>
        public virtual int GetEntityCount<T>(string sqlCondition)
        {
            return this.dalHelper.GetEntityCount<T>(sqlCondition);
        }

        /// <summary>
        /// 检测记录是否存在
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public bool ExistsEntity<T>(string condition)
        {
            return this.dalHelper.ExistsEntity<T>(condition);
        }

        /// <summary>
        /// 获取分类数据
        /// </summary>
        /// <typeparam name="V">上级编号的数据类型</typeparam>
        /// <param name="sqlTemplate">
        /// 查询模板
        /// <para>如：select [id],[title] from [tmenu] where [parentid]={0} order by id</para>
        /// <para>如：select [id],[title] from [tmenu] where [parentid]='{0}' order by id</para>
        /// </param>
        /// <param name="parentID">int类型或者Guid类型的值</param>
        /// <param name="topLevelName">顶级分类名称，默认是：请选择分类</param>
        /// <param name="onlyTopCatalog">是否只读取一层</param>
        /// <param name="nullvalue"></param>
        /// <param name="templateid">0=div,1=select</param>
        /// <returns></returns>
        public string WriteEntityCatalogData<V>(string sqlTemplate, object parentID = null, string topLevelName = "", bool onlyTopCatalog = false, bool nullvalue = false, short templateid = 0)
        {
            if (parentID == null && typeof(V) == typeof(int)) parentID = 0;
            if (parentID == null && typeof(V) == typeof(Guid)) parentID = Guid.Empty; // 00000000-0000-0000-0000-000000000000
            string sql = nullvalue ? sqlTemplate.Replace("='{0}'", " is null") : sqlTemplate.Fmt(parentID);
            string topname = topLevelName.Length == 0 ? "请选择分类" : topLevelName;
            string topid = typeof(V) == typeof(int) ? "0" : Guid.Empty.ToString();
            switch (templateid)
            {
                case 1: return WriteEntityCatalogDataAsSelect(sqlTemplate, sql, topid, topname, onlyTopCatalog);
                default: return WriteEntityCatalogDataAsDiv(sqlTemplate, sql, topid, topname, onlyTopCatalog);
            }
        }

        public DataTable GetEntityCatalogData<V>(string sqlTemplate, object parentID = null, string topLevelName = "", bool onlyTopCatalog = false, bool nullvalue = false)
        {
            if (parentID == null && typeof(V) == typeof(int)) parentID = 0;
            if (parentID == null && typeof(V) == typeof(Guid)) parentID = Guid.Empty; // 00000000-0000-0000-0000-000000000000
            string sql = nullvalue ? sqlTemplate.Replace("='{0}'", " is null") : sqlTemplate.Fmt(parentID);
            string topname = topLevelName.Length == 0 ? "请选择分类" : topLevelName;
            string topid = typeof(V) == typeof(int) ? "0" : Guid.Empty.ToString();

            DataTable oDt = this.dbHelper.ExecuteDataTable(sql);
            if (oDt.Rows.Count == 0) return oDt;

            DataTable dtOut = new DataTable();
            dtOut.Columns.Add("ID");
            dtOut.Columns.Add("Name");
            dtOut.Rows.Add(topid, topname);
            foreach (DataRow oRow in oDt.Rows)
            {
                dtOut.Rows.Add(oRow[0], oRow[1]);
                DataTable oDt2 = this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(oRow[0]));
                if (onlyTopCatalog)
                {
                    continue;
                }
                foreach (DataRow oRow2 in oDt2.Rows)
                {
                    dtOut.Rows.Add(oRow2[0], "|----{0}".Fmt(oRow2[1]));
                    DataTable oDt3 = this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(oRow2[0]));
                    foreach (DataRow oRow3 in oDt3.Rows)
                    {
                        dtOut.Rows.Add(oRow3[0], "|------{0}".Fmt(oRow3[1]));
                        DataTable oDt4 = this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(oRow3[0]));
                        foreach (DataRow oRow4 in oDt4.Rows)
                        {
                            dtOut.Rows.Add(oRow4[0], "|--------{0}".Fmt(oRow4[1]));
                            DataTable oDt5 = this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(oRow4[0]));
                            foreach (DataRow oRow5 in oDt5.Rows)
                            {
                                dtOut.Rows.Add(oRow5[0], "|----------{0}".Fmt(oRow5[1]));
                            }
                        }
                    }
                }
            }
            return dtOut;
        }

        private string WriteEntityCatalogDataAsSelect(string sqlTemplate, string toplevelsql, string toplevelid, string toplevelname, bool onlyTopCatalog)
        {
            DataTable oDt = this.dbHelper.ExecuteDataTable(toplevelsql);
            if (oDt.Rows.Count == 0) return "";

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("<option value=\"{0}\">{1}</option>", toplevelid, toplevelname);
            foreach (DataRow oRow in oDt.Rows)
            {
                sb.AppendFormat("<option value=\"{0}\">|--{1}</option>", oRow[0], oRow[1]);
                DataTable oDt2 = this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(oRow[0]));
                if (onlyTopCatalog)
                {
                    continue;
                }
                foreach (DataRow oRow2 in oDt2.Rows)
                {
                    sb.AppendFormat("<option value=\"{0}\">|----{1}</option>", oRow2[0], oRow2[1]);
                    DataTable oDt3 = this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(oRow2[0]));
                    foreach (DataRow oRow3 in oDt3.Rows)
                    {
                        sb.AppendFormat("<option value=\"{0}\">|------{1}</option>", oRow3[0], oRow3[1]);
                        DataTable oDt4 = this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(oRow3[0]));
                        foreach (DataRow oRow4 in oDt4.Rows)
                        {
                            sb.AppendFormat("<option value=\"{0}\">|--------{1}</option>", oRow4[0], oRow4[1]);
                            DataTable oDt5 = this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(oRow4[0]));
                            foreach (DataRow oRow5 in oDt5.Rows)
                            {
                                sb.AppendFormat("<option value=\"{0}\">|----------{1}</option>", oRow5[0], oRow5[1]);
                            }
                        }
                    }
                }
            }
            return sb.ToString();
        }

        private string WriteEntityCatalogDataAsDiv(string sqlTemplate, string toplevelsql, string toplevelid, string toplevelname, bool onlyTopCatalog)
        {
            DataTable oDt = this.dbHelper.ExecuteDataTable(toplevelsql);
            if (oDt.Rows.Count == 0) return "";

            StringBuilder sb = new StringBuilder();
            sb.Append("<div>");
            sb.AppendFormat("<div class=\"lev1\"><label id=\"{1}\">{0}</label></div>", toplevelname, toplevelid);
            //sb.Append("<div class=\"lev1\"><label id=\"0\">" + (topLevelName.Length == 0 ? "请选择分类" : topLevelName) + "</label></div>");
            foreach (DataRow oRow in oDt.Rows)
            {
                sb.Append("<div class=\"lev1\">");
                sb.AppendFormat("<label id=\"{0}\">{1}</label>", oRow[0], oRow[1]);
                DataTable oDt2 = this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(oRow[0]));
                if (oDt2.Rows.Count > 0) sb.Append("<a href=\"###\" class=\"v\">[-]</a>");
                if (onlyTopCatalog)
                {
                    sb.Append("</div>");
                    continue;
                }
                foreach (DataRow oRow2 in oDt2.Rows)
                {
                    sb.Append("<div class=\"lev2 lev\">");
                    sb.AppendFormat("<label id=\"{0}\">{1}</label>", oRow2[0], oRow2[1]);
                    DataTable oDt3 = this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(oRow2[0]));
                    if (oDt3.Rows.Count > 0) sb.Append("<a href=\"###\" class=\"v\">[-]</a>");
                    foreach (DataRow oRow3 in oDt3.Rows)
                    {
                        sb.Append("<div class=\"lev3 lev\">");
                        sb.AppendFormat("<label id=\"{0}\">{1}</label>", oRow3[0], oRow3[1]);
                        DataTable oDt4 = this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(oRow3[0]));
                        if (oDt4.Rows.Count > 0) sb.Append("<a href=\"###\" class=\"v\">[-]</a>");
                        foreach (DataRow oRow4 in oDt4.Rows)
                        {
                            sb.Append("<div class=\"lev4 lev\">");
                            sb.AppendFormat("<label id=\"{0}\">{1}</label>", oRow4[0], oRow4[1]);
                            DataTable oDt5 = this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(oRow4[0]));
                            if (oDt5.Rows.Count > 0) sb.Append("<a href=\"###\" class=\"v\">[-]</a>");
                            foreach (DataRow oRow5 in oDt5.Rows)
                            {
                                sb.Append("<div class=\"lev5 lev\">");
                                sb.AppendFormat("<label id=\"{0}\">{1}</label>", oRow5[0], oRow5[1]);
                                sb.Append("</div>");
                            }
                            sb.Append("</div>");
                        }
                        sb.Append("</div>");
                    }
                    sb.Append("</div>");
                }
                sb.Append("</div>");
            }
            sb.Append("</div>");
            return sb.ToString();
        }

        /// <summary>
        /// 获取EXXML字段的实体数据对象
        /// </summary>
        /// <typeparam name="X"></typeparam>
        /// <param name="xmlText"></param>
        /// <returns></returns>
        public X GetEntityXMLFieldEntity<X>(string xmlText) where X : new()
        {
            if (xmlText == null || xmlText.Length == 0) return default(X);
            return new XMLHelper().GetXMLEntity<X>(xmlText);
        }

        /// <summary>
        /// 指定一条SQL命令
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteSql(string sql)
        {
            return this.dbHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 获取分类树形数据(Json格式)
        /// <para>todo:去除rootname参数</para>
        /// </summary>
        /// <typeparam name="V">上级编号的数据类型</typeparam>
        /// <param name="sqlTemplate">
        /// 查询模板
        /// <para>如：select [id],[title] from [tmenu] where [parentid]={0} order by id</para>
        /// <para>如：select [id],[title] from [tmenu] where [parentid]='{0}' order by id</para>
        /// </param>
        /// <param name="parentID">int类型或者Guid类型的值</param>
        /// <param name="rootname">顶级分类名称，默认是：请选择分类</param>
        /// <param name="onlyTopCatalog">是否只读取一层</param>
        /// <returns></returns>
        public string GetJsonTreeData(string sqlTemplate, Guid parentID, string rootname = "", bool onlyTopCatalog = false)
        {
            if (sqlTemplate.IndexOf("{0}") == -1) return "{n:'',v:'禁止死循环查询！'}";
            StringBuilder sb = new StringBuilder();
            string topname = rootname.Length == 0 ? "请选择分类" : rootname;
            sb.Append("[");
            foreach (DataRow dr in this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(parentID)).Rows)
            {
                sb.AppendFormat("{{n:'{1}',v:'{0}',c:{2}}},", dr[0], dr[1], this.GetJsonTreeData(sqlTemplate, (Guid)dr[0], rootname, onlyTopCatalog));
            }
            sb.Append("]");
            sb.Replace("},]", "}]");
            return sb.ToString();
        }

        /// <summary>
        /// 获取分类树形数据(XML格式)
        /// </summary>
        /// <param name="sqlTemplate"></param>
        /// <param name="parentID"></param>
        /// <param name="onlyTopCatalog"></param>
        /// <returns></returns>
        public string GetXmlTreeText(string sqlTemplate, Guid parentID, bool onlyTopCatalog = false)
        {
            if (sqlTemplate.IndexOf("{0}") == -1) return "<node ID=\"{0}\" Name=\"{1}\" />".Fmt(Guid.Empty, "禁止死循环查询！");
            StringBuilder sb = new StringBuilder();
            foreach (DataRow dr in this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(parentID)).Rows)
            {
                sb.AppendFormat("<node ID=\"{0}\" Name=\"{1}\">", dr[0], dr[1]);
                if (!onlyTopCatalog) sb.Append(GetXmlTreeText(sqlTemplate, dr[0].ToString().ToGuid(), onlyTopCatalog));
                sb.Append("</node>");
            }
            return sb.ToString();
        }

        /// <summary>
        /// 获取所有父级编号列表(包括自己)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public IList<Guid> GetParentIds<T>(Guid id)
        {
            if (id == Guid.Empty) return new List<Guid>();
            IList<Guid> list = new List<Guid>() { id };
            string sql = "select ParentID from [{0}] where ID='{{0}}'".Fmt(typeof(T).Name);
            this.GetParentIdsFnc(ref list, sql, id);
            return list;
        }
        private void GetParentIdsFnc(ref IList<Guid> list, string tplSql, Guid id)
        {
            foreach (DataRow r in this.dbHelper.ExecuteDataTable(tplSql.Fmt(id)).Rows)
            {
                list.Add(r[0].ToString().ToGuid());
                this.GetParentIdsFnc(ref list, tplSql, r[0].ToString().ToGuid());
            }
        }
        // 需要coid
        public IList<Guid> GetParentIdsWithCoid<T>(string coid,Guid id)
        {
            if (id == Guid.Empty) return new List<Guid>();
            IList<Guid> list = new List<Guid>() { id };
            string sql = "select ParentID from [{0}] where ID='{{0}}' and CoID='{1}'".Fmt(typeof(T).Name,coid);
            this.GetParentIdsFnc(ref list, sql, id);
            return list;
        }

        /// <summary>
        /// 获取所有子级编号列表(不包括自己)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parentID"></param>
        /// <param name="parentfieldname"></param>
        /// <param name="idfieldname"></param>
        /// <returns></returns>
        public IList<Guid> GetChildIDs<T>(Guid parentID,string parentfieldname="ParentID", string idfieldname="ID")
        {
            IList<Guid> list = new List<Guid>();
            string sql = "select {1} from [{0}] where {2}='{{0}}'".Fmt(typeof(T).Name,idfieldname,parentfieldname);
            this.GetChildIDsFnc(ref list, sql, parentID);
            return list;
        }
        private void GetChildIDsFnc(ref IList<Guid> list, string sqlTemplate, Guid parentID)
        {
            foreach (DataRow dr in this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(parentID)).Rows)
            {
                list.Add(dr[0].ToString().ToGuid());
                GetChildIDsFnc(ref list, sqlTemplate, (Guid)dr[0]);
            }
        }
        public IList<Guid> GetChildIDsWithCoID<T>(string coid,Guid parentID, string parentfieldname = "ParentID", string idfieldname = "ID")
        {
            IList<Guid> list = new List<Guid>();
            string sql = "select {1} from [{0}] where {2}='{{0}}' and CoID='{3}'".Fmt(typeof(T).Name, idfieldname, parentfieldname,coid);
            this.GetChildIDsFnc(ref list, sql, parentID);
            return list;
        }

        /// <summary>
        /// 获取分类树形数据(DataTable表)
        /// </summary>
        /// <param name="sqlTemplate">如：select ID,Name from {0} where ParentID='{{0}}'</param>
        /// <param name="parentID"></param>
        /// <param name="onlyTopCatalog"></param>
        /// <returns></returns>
        public DataTable GetTreeTable(string sqlTemplate, Guid parentID, bool onlyTopCatalog = false)
        {
            DataTable dt = new DataTable("TreeTable");
            if (sqlTemplate.IndexOf("{0}") == -1) return dt;
            this.GetTreeTableFnc(ref dt, sqlTemplate, parentID, onlyTopCatalog);
            dt.TableName = "TreeTable";
            return dt;
        }

        /// <summary>
        /// GetTreeTable()子方法
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="sqlTemplate"></param>
        /// <param name="parentID"></param>
        /// <param name="onlyTopCatalog"></param>
        /// <param name="loop"></param>
        private void GetTreeTableFnc(ref DataTable dataTable, string sqlTemplate, Guid parentID, bool onlyTopCatalog = false, int loop = 0)
        {
            if (dataTable.Columns.Count == 0)
            {
                dataTable = this.dbHelper.ExecuteDataTable(DatabaseHelper.SetLimit(sqlTemplate, 0).Fmt(Guid.Empty));
            }
            DataRow r;
            foreach (DataRow dr in this.dbHelper.ExecuteDataTable(sqlTemplate.Fmt(parentID)).Rows)
            {
                r = dataTable.NewRow();
                r.ItemArray = dr.ItemArray;
                r["Name"] = "{0}{1}".Fmt("--".Repeat(loop), r["Name"]);
                dataTable.Rows.Add(r);
                if (!onlyTopCatalog)
                {
                    GetTreeTableFnc(ref dataTable, sqlTemplate, (Guid)dr[0], onlyTopCatalog, loop + 1);
                }
            }
        }

        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            return this.dbHelper.ExecuteDataTable(sql);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="name"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public Dictionary<string, object> ExecSP(string name, params DbParameter[] args)
        {
            return this.dbHelper.ExecuteProcedureNonQuery(name, args);
        }
    }
}
